Advanced search

It is possible to translate a field's system name. When performing a search in a multilingual system, either use the field's English system name or the translated system name in the current data language. Alternatively, use the field's tag, which is the primary identifier of a field and it is the same across all languages.

There are a number of ways to identify a field's system name and tag (full details here).

The quickest and simplest way is to hover the mouse cursor over a field in Record details View or Result set View: a tooltip will display the field's system name in the current data language and its tag.

This feature is available in different modes and views, including:

• In Record details View when viewing records (in Display mode A record is either in Display mode (we view its details) or Edit mode (we add or edit its details). A record enters Edit mode as soon as we create a new record, copy a record in Record details View or edit an existing record.) and adding / editing records (in Edit mode):

  

• In Result set View:

  

1. Scroll through the Fields list and double-click the system name of the field to search

   -OR-

   Filter the list of system names by typing into the text box above the list:

   

   The search field's system name is added to the Search statement box:

   

Rather than selecting a name from the Fields list, you can type a system name or tag directly into the Search statement box.

For example, the system name and tag for the Name field are name and BA:

We could type either of these into the Search statement box, followed by a space:

Capitalization and punctuation are important!

Be sure to use the correct capitalization and punctuation. In this example, you would specify name.type or do:

Typing name_type or DO will either find nothing because the field does not exist or it will search the wrong field.

By default the Fields list only includes fields that have been indexed. Non-indexed fields can be searched but without an index the search is much slower as Collections must query every record sequentially for your search value.

To add non-indexed fields to the Fields list, select Settings and enable Show non-indexed fields too:

Note: The Fields list does not differentiate between indexed and non-indexed fields. If your search is proving to be slow, it may be that Show non-indexed fields too has been enabled and you have selected a non-indexed field from the Fields list.

With these operators you specify a value and search a field for that value:

Operator	Search statement symbol / term	Details
is	= or equals	Depending on how the field you are searching has been indexed, your search value: must exactly match the entire contents of the field you are searching (typically Linked fields A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref)., e.g. Creator (creator (VV)), Author (author.name (ya)) -OR- must match exactly with a value found somewhere in the field you are searching (typically long text fields: notes, description and book titles for instance). A useful and powerful search operator, especially when used in conjunction with the * wildcard. Rule of thumb A free text field (notes, description and book titles for instance) can be searched for values found in the field, a single word for instance; when searching a Linked field A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). your search value must exactly match the entire contents of the field. In either case, the * wildcard can be extremely useful. The * substitutes zero or more characters at its position in a search value, so rather than searching for "Wood, Gerard" in a Linked field, a search for: creator = wood,* will return any record for creators with the last name wood (assuming creator names are formatted as surname, firstname). Note: is also performs the same search as the equivalent operator. In the Thesaurus a term can have at least one Equivalent Term (Equivalent (equivalent_term (et)) defined; in Persons and institutions a name can have at least one Equivalent Name (Equivalent (equivalent_name (et)) defined. When using the is operator to search for a term / name, a search is performed for it AND all of its equivalent terms / names. Details An overview of Indexing When a field is indexed its data is copied to a table and optimized for searching; when you search an indexed field, you search the optimized index rather than the field itself. There is more to indexing than that but the key point is that indexing a field makes a search in the field quick and efficient (more details can be found in the Axiell Designer Help). Not all fields in Collections are indexed. When you search a data source The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on. using the Standard tab of the Search box, only indexed fields are made available for searching. On the Advanced tab it is possible to search non-indexed fields by selecting Settings and enabling Show non-indexed fields too, however searching a non-indexed field can be slow (a sequential search of the data across all records is performed). Incidentally, when you use the contains search operator, you effectively circumvent a field's index. For our purposes, a field can be indexed in one of two ways: the entire contents of the field is indexed as a single value (Text (term)) or each word in a field is indexed separately (Free text). When constructing an is search some awareness of how a field might be indexed is useful. Short of accessing the Collections data dictionary in Axiell Designer A tool for designing, creating, customizing and managing Axiell Collections applications and databases, broadly speaking, the Axiell Collections Model Application. As well as managing databases, including user access and permissions, Designer is used for such tasks as translating field labels, tooltips, values in drop lists, etc., there is no simple way to know how a field has been indexed, but the following guidelines, and testing with your data, will demonstrate the value of the is search operator. Note: A third type of indexing is available from Collections 1.14 onwards, Full Text indexing (details about Full Text indexing here). When implemented, an is / equals search behaves as if Text (term) indexing is implemented. The two types of index are: Index Details Text (term) The entire value in the field is indexed as a single value, including spaces and punctuation. Linked fields A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). such as Object name (object_name (OB)) and Creator (creator (VV)) are indexed in this way. The search value must exactly match the entire contents of the field. If it includes a space, hyphen / minus (-) or plus (+), the entire search value must be enclosed in double quotes. For example, if values are stored in the creator (VV) field as surname, firstname, your search statement would be constructed as: creator = "wood, gerard" This is where the * wildcard can be extremely useful. The * wildcard substitutes zero or more characters at its position in a search value. For example: creator = wood,* will return any record for creators with the last name wood (assuming creator names are formatted as surname, firstname). Searching for data that includes double or single quotes As the entire value in a Text (term) field is indexed, any quotation marks in the field become part of the indexed value and must be included in your search value. For example, if a Text (term) indexed field in a record holds a value like this: “Marital happiness” this search will not locate the record: search field = “Marital happiness” as Collections will assume the special meaning of the quotes applies and will search for: Marital happiness To include the quotations marks in your search value, place single quotes around the entire search value: search field = '“Marital happiness”' If the value includes single quote: 'Marital happiness' place double quotes around the entire search value: search field = "'Marital happiness'" Alternatively, use the * wildcard character to truncate the search, e.g.: = “Marital* Rather than using the * wildcard, you could use the contains search operator. Details below. Free text (aka Word indexed field) Each word in a field is indexed separately. A search value must match exactly with a value found somewhere in the searched field. Free text fields (notes, description and book titles for instance) are typically indexed in this way. As each word is indexed separately, you can include multiple values in any order. However all specified values must be matched for a record to be returned by this search. When searching for more than one word, enclose the entire search value in double quotes (see 3. Specify the search value for more details). Note: A Boolean AND search applies when including more than one value in the search field: every value must be found in order for a record to be returned by the search. For example, any title that includes the two words the and and in any order will be matched by this search: title = "the and" If a title includes the but not and, it will not be returned by this search. Each search value must match exactly with a value found in the searched field: the will match the but not other for instance. Again, the * wildcard can be employed. In this case, *the* will match the, other, their, etc.: title = "*the* and" Notes Punctuation enclosed by spaces (i.e. with a space on either side) or quotes (double or single) around words in your record data are not indexed and therefore cannot be searched for with equals / is. To search for them, use contains. When using the * wildcard to truncate a search value in a long text field, your search value will only be located if it is in the first 100 characters of the field. Instead, use the contains / contains all search operator (an _) as this limitation does not apply.
starts with2 Collections version 1.16 onwards when searching a Full Text indexed database.	startswith	Return records where the value in the search fields starts with the specified character(s). Details starts with is useful when searching long text fields as it allows you to search for the first word in the field, in contrast to is / equals, which searches for your search term anywhere in the field. Values are implicitly truncated, and a search for littl operates as if the * wildcard has been used, e.g. littl*. This example search would be: title startswith littl See wildcards for more details about truncation.
contains	_ (an underscore) / contains	contains or contains all Collections 1.16 saw the introduction of the contains any search operator for searching Full Text indexed data sources (details below); from Collections 1.16 onwards contains all is used in place of contains when searching Full Text indexed data sources to better distinguish from contains any. When searching Full text indexed data sources, use contains all; otherwise use contains; they perform the same function and both can be substituted with an underscore ( _ ) in a search statement. With the contains operator a record is returned if the precise sequence of characters in your search value is located anywhere in the search field. If you provide more than one word in your search value, all of them must be found in the search field, although they can appear in any order. For example: title _ and will return any record where the title field includes and, hand, England, understand, etc. title _ "history of Holland" will return A brief history of Holland: from past to present in 100 pages, but it will not return Holland: a brief history. Note: If there are spaces in your search value, you must enclose the search value in double quotes. A contains search can be slow as indexing (which is designed to make a search quick and efficient) is ignored as the entire content of a search field is examined character by character (details about indexing here). The contains operator can therefore be used in both indexed and non-indexed fields. Clearly, there is no need to use the * wildcard character at the beginning or end of your search value (e.g. *wood*) as truncation is always assumed. The * can still be useful within your search value however. For example: title _ pa*er will find painter, paler, partner, patter, etc.
contains all	_ (an underscore) / containsall	contains or contains all Collections 1.16 saw the introduction of the contains any search operator for searching Full Text indexed data sources (details below); from Collections 1.16 onwards contains all is used in place of contains when searching Full Text indexed data sources to better distinguish from contains any. When searching Full text indexed data sources, use contains all; otherwise use contains; they perform the same function and both can be substituted with an underscore ( _ ) in a search statement. As for contains (see above) and a record is returned if the precise sequence of characters in your search value is located anywhere in the search field. If you provide more than one word in your search value, all of them must be found in the search field, although they can appear in any order. Right-truncation of search values with the * wildcard character is allowed (and is in fact explicit so does not need to be specified); left-truncation is not possible. See below for a comparison of containsany, containsall and containsphrase, including truncation. Note: Available from Collections version 1.16 onwards when searching a Full Text indexed data source.
contains any	$ / containsany	A record is returned if any of your search values (you would typically provide more than one) appear anywhere in the search field. If you provide more than one word in your search value, only one of them must be found in the search field. Right-truncation of search values with the * wildcard character is allowed (and is in fact explicit so does not need to be specified); left-truncation is not possible. See below for a comparison of containsany, containsall and containsphrase, including truncation. Note: Available from Collections version 1.16 onwards when searching a Full Text indexed data source.
contains phrase	~ / containsphrase	A record will be returned if the exact combination of words in your search value (ignoring case) is located in the search field (be sure to enclose the phrase in double quotes). For example A contains phrase search for: title containsphrase "dollhouse with furniture" -OR- title ~ "dollhouse with furniture" would return records with titles such as Wooden dollhouse with furniture and lights and Dollhouse with furniture, but not Dollhouse with plastic furniture. Right-truncation of search values with the * wildcard character is allowed (and is in fact explicit so does not need to be specified); left-truncation is not possible. See below for a comparison of containsany, containsall and containsphrase, including truncation. Note: Available from Collections version 1.16 onwards when searching a Full Text indexed data source.

Example search: contains any, contains all and contains phrase

To appreciate the difference between these search operators, consider two records with the following data in the Title (title (TI)) field:

• Record 1: buster keaton
• Record 2: diane keaton

Operator	Search statement	Records returned	Details
contains all	title containsall "keaton buster" -OR- title _ "keaton buster"	Record 1	Only Record 1 includes both buster and keaton in the title (note that the search values can appear in any order in the search field).
contains any	title containsany "keaton buster" -OR- title $ "keaton buster"	Records 1 and 2	Both records include at least one of the search values, keaton, in the title.
contains phrase	title containsphrase "keaton buster" -OR- title ~ "keaton buster"	None	No record contains this precise phrase.

Note that right-truncation of search values with the * wildcard character is permitted (and is in fact explicit so does not actually need to be specified), but that left-truncation is not possible. Here we see the previous contains any example with right-truncation:

title containsany "keat* bust*"

-OR-

title $ "keat* bust*"

Does not equal / does not contain

On the Standard search tab searching for records that do not equal / contain a particular value is a simple matter of selecting a search operator and specifying the value to be excluded from search results (details here).

When performing an Advanced search there is no single search operator for does not equal or does not contain. Instead we perform a Boolean AND NOT search, returning all records and excluding those that equal or contain a specified value.

For example:

all AND NOT creator = "wood, gerard"

will return records where the creator is not "wood, gerard".

These search operators are available when searching date, ISO date, integer and numeric fields.

In an ISO date field (Field type = DateIso), your search value can be a partial date: just the year (yyyy) or year followed by a month (yyyy-mm). Details about how to check a field's type here.

As the name indicates, Hierarchical operators are intended for searching data sources The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on. in which records are typically organized in a hierarchy of parent / child relationships - Archives and Locations and containers, for instance. They are also used when searching the authority data sources, Persons and institutions, and especially Thesaurus in which the relationship between records can be defined hierarchically via broader and narrower terms.

Some (though not all) of these operators are useful when directly searching a hierarchical data source (e.g. you selected Thesaurus from the Select data source box); typically, however, they are used when searching a data source that is linked to a hierarchical data source (e.g. you are searching the Object catalogue, which is linked to records in an authority data source).

Operator	Search statement	Details
broader	broader	Search for records higher in the hierarchy than your search value: When searching the Thesaurus or a field linked to the Thesaurus, specify a term as your search value and search for all of its broader terms (where a Broader Term (broader_term (bt)) has been defined for it). Searching the Thesaurus The term Box has a Broader Term and several Narrower Terms: This search in the Thesaurus will return the record for Container: term broader box If we search a field linked to the Thesaurus, this search will return any record where Object name = Container: object_name broader box When searching a data source in which records can be organized in parent / child relationships or searching a field that is linked to such a data source, return records higher in the hierarchy than your search value. Searching a parent / child hierarchy The broader search operator can be used when searching any data source in which records are organized in a hierarchy of parent / child relationships. You search the field that stores the value used when linking records in a parent / child relationship (not the Linked field A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). itself). For example: Archives catalogue: the Part of and Part Linked fields are used to construct a hierarchy of archival records; the value in these fields is the object_number (IN) of an archives record and your search field is therefore object_number (IN). Locations and containers: the part_of (2A) and part (nt) Linked fields are used to construct a hierarchy of location records; the value in these fields is the name (te) of a location and your search field is therefore name (te). For example, locations are organized in a hierarchy of parent / child records in Locations and containers. Here we see five levels in the locations hierarchy of the National Museum. The top level of the hierarchy is the museum itself; the museum has two sites, the Main Campus and Off-site storage; we drill down through another three levels on the Main Campus branch of the hierarchy to individual shelves (we could go further and register boxes on shelves for instance): National Museum (NM) Main Campus (NM/MC) Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) First Floor (NM/MC/FF) Northend Hall (NM/MC/FF/NH)... Southend Hall (NM/MC/FF/SH)... Off-site storage (NM/OS)... This search in Locations and containers will return the two highlighted records: name broader "ground floor" Note: Double quotes are required around the search value because it includes a space.
narrower	narrower	Search for records lower in the hierarchy than your search value: When searching the Thesaurus or a field linked to the Thesaurus, specify a term as your search value and search for all of its narrower terms (where a Narrower Term (narrower_term (nt)) has been defined for it). Searching the Thesaurus The term Box has a Broader Term and several Narrower Terms: This search in the Thesaurus will return six records, one for each of the narrower terms: term narrower box If we search a field in the Object catalogue that is linked to the Thesaurus, any record with one of the six narrower terms in the search field will be returned by this search. If we search a field linked to the Thesaurus, this search will return any record where Object name is any one of box's six narrower terms : object_name narrower box When searching a data source in which records can be organized in parent / child relationships or searching a field that is linked to such a data source, return records lower in the hierarchy than your search value. Searching a parent / child hierarchy The narrower search operator can be used when searching any data source in which records are organized in a hierarchy of parent / child relationships. You search the field that stores the value used when linking records in a parent / child relationship (not the Linked field A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). itself). For example: Archives catalogue: the Part of and Part Linked fields are used to construct a hierarchy of archival records; the value in these fields is the object_number (IN) of an archives record and your search field is therefore object_number (IN). Locations and containers: the part_of (2A) and part (nt) Linked fields are used to construct a hierarchy of location records; the value in these fields is the name (te) of a location and your search field is therefore name (te). For example, locations are organized in a hierarchy of parent / child records in Locations and containers. Here we see five levels in the locations hierarchy of the National Museum. The top level of the hierarchy is the museum itself; the museum has two sites, the Main Campus and Off-site storage; we drill down through another three levels on the Main Campus branch of the hierarchy to individual shelves (we could go further and register boxes on shelves for instance): National Museum (NM) Main Campus (NM/MC) Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) First Floor (NM/MC/FF) Northend Hall (NM/MC/FF/NH)... Southend Hall (NM/MC/FF/SH)... Off-site storage (NM/OS)... This search in Locations and containers will return the six highlighted records: name narrower "ground floor" Note: Double quotes are required around the search value because it includes a space.
topterm	topterm	Search for the topmost record in the hierarchy in which your search value is located: When searching the Thesaurus or a field linked to the Thesaurus, specify a term as your search value and search for its topmost broader term; if the search value does not have a broader term (i.e. it is the topmost term), it will be searched for. If the topmost term has an Equivalent Term (equivalent_term (et)) , the equivalent term will also be searched for. Searching the Thesaurus The term Box has a Broader Term, Container, and several Narrower Terms: The term Container has an Equivalent Term, Storage container: This search in the Thesaurus will return the record for Container and its equivalent term, Storage container: term topterm "Tool box" If we search a field linked to the Thesaurus, this search will return any record where Object name = Container or Storage container: object_name topterm "Tool box" Note: Double quotes are required around the search value because it includes a space. When searching a data source in which records can be organized in parent / child relationships or searching a field that is linked to such a data source, return the record at the top of the hierarchy in which your search value is located. Searching a parent / child hierarchy The topterm search operator can be used when searching any data source in which records are organized in a hierarchy of parent / child relationships. You search the field that stores the value used when linking records in a parent / child relationship (not the Linked field A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). itself). For example: Archives catalogue: the Part of and Part Linked fields are used to construct a hierarchy of archival records; the value in these fields is the object_number (IN) of an archives record and your search field is therefore object_number (IN). Locations and containers: the part_of (2A) and part (nt) Linked fields are used to construct a hierarchy of location records; the value in these fields is the name (te) of a location and your search field is therefore name (te). For example, locations are organized in a hierarchy of parent / child records in Locations and containers. Here we see five levels in the locations hierarchy of the National Museum. The top level of the hierarchy is the museum itself; the museum has two sites, the Main Campus and Off-site storage; we drill down through another three levels on the Main Campus branch of the hierarchy to individual shelves (we could go further and register boxes on shelves for instance): National Museum (NM) Main Campus (NM/MC) Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) First Floor (NM/MC/FF) Northend Hall (NM/MC/FF/NH)... Southend Hall (NM/MC/FF/SH)... Off-site storage (NM/OS)... This search in Locations and containers will return the highlighted record: name topterm "ground floor" Note: Double quotes are required around the search value because it includes a space.
generic	generic	Similar to narrower except that you search for your search value and any records lower in the hierarchy than your search value: When searching the Thesaurus or a field linked to the Thesaurus, specify a term as your search value and search for it and all of its narrower terms (where a Narrower Term (narrower_term (nt)) has been defined for it). Searching the Thesaurus The term Box has a Broader Term and several Narrower Terms: This search in the Thesaurus will return seven records, the record for Box and one for each of the narrower terms: term generic box If we search a field linked to the Thesaurus, this search will return any record where Object name = box or any one of box's six narrower terms: object_name generic box When searching a data source in which records can be organized in parent / child relationships or searching a field that is linked to such a data source, return the record for your search value and records lower in the hierarchy than your search value. Searching a parent / child hierarchy The generic search operator can be used when searching any data source in which records are organized in a hierarchy of parent / child relationships. You search the field that stores the value used when linking records in a parent / child relationship (not the Linked field A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). itself). For example: Archives catalogue: the Part of and Part Linked fields are used to construct a hierarchy of archival records; the value in these fields is the object_number (IN) of an archives record and your search field is therefore object_number (IN). Locations and containers: the part_of (2A) and part (nt) Linked fields are used to construct a hierarchy of location records; the value in these fields is the name (te) of a location and your search field is therefore name (te). For example, locations are organized in a hierarchy of parent / child records in Locations and containers. Here we see five levels in the locations hierarchy of the National Museum. The top level of the hierarchy is the museum itself; the museum has two sites, the Main Campus and Off-site storage; we drill down through another three levels on the Main Campus branch of the hierarchy to individual shelves (we could go further and register boxes on shelves for instance): National Museum (NM) Main Campus (NM/MC) Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) First Floor (NM/MC/FF) Northend Hall (NM/MC/FF/NH)... Southend Hall (NM/MC/FF/SH)... Off-site storage (NM/OS)... This search in Locations and containers will return the seven highlighted records: name generic "ground floor"
equivalent	equivalent	Can be used to search the Thesaurus and Persons and institutions or a field linked to a record in either of these data sources The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on.. In the Thesaurus a term can have at least one Equivalent Term (equivalent_term (et)) defined; in Persons and institutions a name can have at least one Equivalent Name (equivalent_name (et)) defined. Specify a term / name as your search value and search for it AND all of its equivalent terms / names. Note: The equals operator performs this same function. Details The term Box has an Equivalent Term of Carton: This search in the Thesaurus will return the records for box and Carton: term equivalent box If we search a field linked to the Thesaurus, this search will return any record where Object name = box or Carton: object_name equivalent box
related	related	Can be used to search the Thesaurus or a field linked to a record in the Thesaurus where a term has at least one Related Term (related_term (rt)) defined for it. Specify a term as your search value and search for it AND all of its related terms. Details The term Box has a Related Term of Crate: This search in the Thesaurus will return the records for box and Crate: term related box If we search a field linked to the Thesaurus, this search will return any record where Object name = box or Crate: object_name related box
pseudonym	pseudonym	Can be used to search the Persons and institutions or a field linked to Persons and institutions where a name has at least one Pseudonym (pseudonym (PS)) defined for it and the names are in the same domain (i.e. they have the same name.type (do)). Specify a name as your search value and search for it AND all of its pseudonyms Details This search in Persons and institutions will return two records, the record for Wood, Gerard and Wood, Ged: name pseudonym "Wood, Gerard" If we search a field linked to Persons and institutions, this search will return any record where Creator = Wood, Gerard or Wood, Ged: creator pseudonym "Wood, Gerard" Note: Double quotes are required around the search value because it includes a space.
hierarchical	hierarchical	Available when searching any data source The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on. in which records are organized in a hierarchy of parent / child relationships, notably Archives and Locations and containers. With the hierarchical search operator, your search value can be a single record in a hierarchy (e.g. a specific location) or a record in a hierarchy AND all of its children. A typical use of hierarchical is searching for records by their location. Details To demonstrate the use of hierarchical we search on current location, but the same approach applies when searching archives. Background (but worth understanding!) Locations are organized in a hierarchy of parent / child records in the Locations and containers data source. Here we see five levels in the locations hierarchy of the National Museum. The top level of the hierarchy is the museum itself; the museum has two sites, the Main Campus and Off-site storage; here we drill down through another three levels on the Main Campus branch of the hierarchy to individual shelves (we could go further and register boxes on shelves for instance): National Museum (NM) Main Campus (NM/MC) Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) First Floor (NM/MC/FF) Northend Hall (NM/MC/FF/NH)... Southend Hall (NM/MC/FF/SH)... Off-site storage (NM/OS)... When the Current location of an item in your collection is updated, only the lowest level of the hierarchy (the actual location) is stored in the record in the current_location.name (2A) field: Although the full hierarchy is pulled dynamically from the location record and displayed below the current location in a merged-in field, this information is not editable or searchable (details about merged-in fields can be found here). A problem arises if the current location is not a unique name, Shelf 1 for instance. As we see in the hierarchy above, there are two locations called Shelf 1 and if we search for Shelf 1, we will return records in both locations, which is unlikely to be our objective. With the hierarchical search operator: You specify the full location hierarchy as your search value -AND- Search for records at a specific location OR at that location and all levels directly below it (i.e. in the same branch of the hierarchy). To demonstrate, we search the Object catalogue using the Current location name (current_location.name (2A)) field. Unless the location name is unique, you must specify the full pathway to the location as your search value. In the example above, there are several Shelf 1 locations, and specifying Shelf 1 as your search value will return records in each location with this name, which is unlikely to be your objective. Note: If the full pathway to a location is complex, you may find the Standard search tab to be a simpler environment to use the hierarchical search operator as it allows you to search for and select a location using the Find data for the field box. Details here. Specifying the level of the hierarchy you want to search for looks like this: current_location.name hierarchical "National Museum/Main Campus/Ground Floor" Note: Double quotes are required around the search value because it includes spaces and/or forward slashes. Having entered the level of the hierarchy you want to search for, you can modify the search value to narrow or expand your search results: How to modify the search value With the addition of the * wildcard character and forward slash / (or both) to the end of your search value, you can fine-tune your search results. Consider this locations hierarchy: National Museum (NM) Main Campus (NM/MC) Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) First Floor (NM/MC/FF) Northend Hall (NM/MC/FF/NH)... Southend Hall (NM/MC/FF/SH)... Off-site storage (NM/OS)... Search value Details Without / and * Example search value National Museum/Main Campus/Ground Floor Result Search on the last level of the hierarchy (Ground Floor in this example) and all levels directly below it (i.e. in the same branch of the hierarchy). Objects located at any of these levels of the hierarchy will be returned by this search: Ground Floor (NM/MC/GF) Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) With a / Example search value National Museum/Main Campus/Ground Floor/ Result Search on the last level of the hierarchy only. Only objects located on the Ground Floor will be returned by this search. With both / and * Example search value National Museum/Main Campus/Ground Floor/* Result Search on all levels of the hierarchy directly below the last level. Objects located at any of these levels of the hierarchy will be returned by this search: Special Exhibitions Room (NM/MC/GF/SER) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2) Gallery 1 (NM/MC/GF/G1) Shelf 1 (Shelf 1) Shelf 2 (Shelf 2)

When configured by your Application Administrator, certain special characters (listed below) can be omitted from a search value4 Only in Collections version 1.15 onwards where Full Text indexing has been implemented and the normalization update procedure has been run..

Without the update, it is necessary to know the exact spelling of a name like O'Toole, Peter, for example, and to include the single quote and comma in the search value in order to have a precise match:

"O'Toole, Peter"

And if a value includes brackets, hyphens, and so on, it is necessary to include those characters in the search value it is of course possible to exclude characters and still get a match using the wildcard characters * and /or ? in a search value.

With the update, it is possible to perform a search for values with or without the special characters; furthermore, it is possible to use the normalized form of letters (for example, ae will match æ; oe will match Ø or œ).

A space is not considered a special character so must be included in search values comprising multiple terms, but when this option is implemented O'Toole, Peter will be matched by any of the following:

• "O'Toole, Peter"
• "OToole Peter"
• "O'Toole Peter"
• "otool peter"
• otool*

Following the update, the following characters or combinations of characters can be omitted when searching a Full Text indexed database:

Special characters

[

]

;

,

!

@

(

)

|

{

}

<

>

?

\r

\n

\t

`

-

=

\\

.

/

~

#

$

%

^

&

_

+

:

\"

\'

*

You will note that the * and ? are listed as symbols that can be omitted from a search. This is not, of course, in their capacity as wildcards: if you include these characters in your search value, they will continue to behave as wildcards.

However, consider a situation where there are three terms in the Thesaurus: ABCDEF, ABC? and ABC*.

Following the update, a search for ABC* will continue to match all three terms (the * operates as a wildcard character).

A search for ABC however will match ABC? and ABC*.

The exception

The exception is uniquely indexed fields (fields which are both indexed and only permit unique values): in this case it is not possible to omit a special character. For example, if a uniquely indexed object number contains a hyphen:

ABC-1234

searching for ABC1234 will not locate the record. The hyphen must be specified for a direct match; alternatively, using a wildcard, e.g. ABC*, will return the record (along with any others where the object number commences with ABC).

Diacritics

Note that searches were already case insensitive, accents were ignored in search values, and ligatures and other special letters could be matched using the normalized version of the character(s). For example:

• a will match á or Á
• ae will match æ
• oe will match Ø or œ

Searching specifically for a special character

With the exception of the two wildcard characters (* and ?) it is possible to search for a specific special character (in a Full Text indexed database) in both an Advanced and Standard search.

In an Advanced search it is necessary to enclose the special character in quotes and asterisks. For example, to search for a square bracket:

title = "*[*"

To search for an ellipsis:

title = "*…*"

In a Standard search the enclosing quotes are not required:

If your search value includes spaces, the entire search value must be enclosed in quotation marks, for example:

title = "cup saucer"

As we saw above, the indexing of a field affects how you can search a field:

• In a Free text indexed field each word in the field is indexed separately and you can search on any number of words separated by a space:

  Searching for multiple values in Free text indexed fields

  It is possible to search Free text indexed fields for two or more values by enclosing the search terms in double quotes. For example:

  title = "cup saucer"

  will return any records that contain the words cup and saucer anywhere and in any order in the title field. Each search value must match exactly with a value found in the searched field: cup will match cup but not cups for instance.

  Truncation with the * wildcard character is possible on each and every term in your search value. For example:

  title = "cup* saucer"

  will return any records that contain the words cup, cups, cuppa, etc. and saucer anywhere and in any order in the title field.

  Note: It is not necessary to use * with contains as truncation is always assumed.

• In a Text (term) indexed field the entire value in the field is indexed as a single value and your search value must match the data in the search field exactly, including spaces and punctuation:

  Searching for multiple values in Text (term) indexed fields

  As we have seen, the entire value in a Text (term) indexed field is indexed as a single value, including spaces and punctuation. While it is possible to search on multiple terms by enclosing them in double quotes, a record will be returned only if your search value exactly matches the data in the search field. For example, if a creator is recorded as Wood, Gerard, this search will return the record:

  creator = "Wood, Gerard"

  but this will not:

  creator = "Gerard Wood"

  Truncation with the * wildcard character is possible but can only occur on the last term in your search value. For example:

  creator = "Wood, G*"

  would return records for Wood, Gerard; Wood, Graham; Wood, Georgina, etc.

  Note that the * is added inside the double quotes.

Note: You may find that some fields presented as Date fields are in fact Text fields (details about identifying a field's data type here). Here we describe searching for dates in fields with a Date data type.

Although date fields in Collections can be configured to accept dates in a number of formats, they typically have an DateIso data format and are stored in your database with a format of yyyy-mm-dd.

Fields with a DateIso data format can also have a presentation format that determines how they display in the User Interface. A date stored in your database as 2020-10-08 can be configured to display using a European date presentation format, such as 08/10/2020.

As a rule, when your search value is a date, you can specify it using the DateIso data format, yyyy-mm-dd or the presentation format.

Exception to the rule

The exception to this rule is a date with the Locale date (long) ISO date presentation format in which days and months are in words, e.g.:

Thursday 8 October 2020

In this case your search value can be in the DateIso data format, yyyy-mm-dd or the Locale date (short) format. The format of Locale date (short) and Locale date (long) are determined by your local Windows date settings.

Note: See Date fields for details about Date data and presentation formats.

Whichever format you use, the date must be enclosed in double quotes:

"2020-02-22"

Dates can be searched using any of the Relational operators:

=, >, <, <= (to), >= (from)

For example, use is / = to search for a particular date:

input.date = "2001-01-01"

or specify a range using:

• >= or from

  and

• <= or to

with a Boolean operator:

input.date from "2001-01-01" AND input.date to "2001-12-31"

Note the construction of a range search:

[search field] from [date] AND [search field] to [date]

Partial date search

Depending on how your system has been configured by your Application Administrator you may be able to specify a partial date as your search value, such as: "2020-12" or 2020, e.g.:

dating.date.start >= 1937

Note: When specifying a year only, it is not necessary to use double quotes around the value.

Notes

• If a partial date search in a Date field is not possible, it may be that the field's Index has not been set to DateIso by your Application Administrator.
• Although it may be possible to perform a partial date search, it is recommended that you always specify a full date as your search value. Partial date searches may not return all the records you might expect if dates in your records are incomplete; how a date field has been configured might also affect partial date searches (this is explained above).

Today's date

Collections provides a special search value, today, for searching a date field for the current date. When your search value is today, it is interpreted as today's date.

This is useful in a Saved search that is run regularly to return records with the current date. For example:

input.date = today

will retrieve all records created today.

By adding or subtracting X from today you can retrieve records with a date X number of days before or after today. For example:

input.date = today-30

will retrieve all records created thirty days ago.

Note: Do not enclose today-30 in single or double quotes.

To retrieve records created in the last thirty days:

input.date from today-30 AND input.date to today

Note

While it might be tempting to shorten this search statement to:

input.date from today-30

this search statement only yields the same result as the previous search statement if the search field cannot contain future dates. This would be the case with input.date but not for delivery dates, for instance. For this reason it is recommended that you use an AND search statement. With AND it is also possible to construct search statements such as:

venue.date.start from today-15 AND venue.date.start to today+21

Date periods expressed as a natural language value, such as 12th century, can be saved and searched for in fields with a type of Period.

Full details about Period fields can be found here.

Although a Period field displays the natural language version of a period, the indexed (searchable) value is stored in the database as an ISO start and end date range (i.e. a numerical value). For example:

When searching a Period field the search value is specified as a complete natural language period, such as 12th century (any years must be specified as digits); do not use single words (not even truncated) from the natural language period (such as century) nor ISO dates with months and/or days.

An overview of English natural language period elements that can be entered and subsequently searched for can be found here.

Behind the scenes the natural language search value is converted to an ISO date range and matched against the indexed values.

Notes:

• When searching a broad period, the exact period specified in the search will be yielded and narrower periods that fall within its range.
• It is only possible to search for a single date period, not date period ranges.
• Values must be specified in the current User Interface language in Collections when searching records.

When specifying a search value with a decimal point in a numeric field, always use a period (dot) as the decimal separator even if it is possible to enter numbers in fields (e.g. monetary amounts) using a different decimal separator, a comma for instance:

subscription_price >= 80.50

To search for a marked checkbox, specify X as your search value, e.g.:

free_field.confidential = X

With the exception noted below, each statement that is combined with a Boolean operator must be a fully formed search statement as described in steps 1 to 3 above (comprising a search field, search operator and search value).

For example, to search records where Object name is photo (photograph, photography, etc.) AND Material is glass plate you would construct two search statements and combine them:

object_name = photo* AND material = “glass plate”

The exception to this rule is when constructing two or more statements that search the same field.

For example, to search for titles with the word festival or fair, a complete search statement would be:

title = festival OR title = fair

but as we are searching the same field, a shorthand is available:

title = festival , fair

A shorthand is available for the AND, OR and AND NOT Boolean operators. Details below.

Where records can be arranged in a hierarchy, a field can be configured to be inherited so that a record lower in the hierarchy inherits a value entered in a record immediately higher in the hierarchy (its parent). This can be efficient as a value only needs to be entered and saved in the parent record to be inherited by all of its children; importantly, while the value displays in a child record it is not stored in that record (in the child record the field is actually empty until the field is edited and the inherited value is overwritten).

In the following example, the Conditions governing access and Conditions governing reproduction fields are configured to be inherited and data has been added to both fields in a parent record:

When we view a record lower in the hierarchy, the values from the parent record are displayed in these two fields but with a dotted underline to indicate that they have been inherited:

Inheritance in older versions of Collections

In older versions of Collections an inherited value is a lighter grey than other data. Here we see the Archival history field in a parent record:

When we view a record lower in the hierarchy, the Archival history value is faded to indicate that it has been inherited:

An inherited value can be edited or deleted in the current record The record currently displayed in Record details View or highlighted (with a solid grey background) in Result set View or Gallery View for instance.. Here we see the Conditions governing reproduction field in Edit mode in a child record:

The value can be deleted entirely, or edited / rewritten. When you edit the record and add a value to an inherited field, the new value will not be underlined / faded as, of course, it is no longer inherited:

Tip: If you delete or edit an inherited value in a child record, it can be restored by right-clicking the field in Edit mode and selecting Restore inheritance from the context menu that displays.

In summary, when data in a field has a dotted underline (or it is faded / lighter grey than other data), it is displayed in the field but not saved in the current record. When you edit the field, the edited value is saved in the current record.

Previously, the Expand operator was used to search for records using one or more values inherited from a parent record but it is no longer required.

When searching for a value in a field configured as inherited, an inherited value is treated as if it is saved in a child record5 Unless your System Administrator has disabled this feature, in which case it is necessary to use Expand to search for records using one or more values inherited from a parent record..

In other words, a search for Title="Creature from Mars" will return a parent record with this title and any of its child records that have inherited this title.

Note: If the search field has not been configured as inherited, you would still use Expand. Details below.

Example

In the Moving image catalogue we have a hierarchy of three records:

The Title field (title (TI)) has been configured to be inherited and a value has been added to the parent record:

This value is inherited by all children (and it has a dotted underline to indicate that it is inherited):

A Standard search for:

or an Advanced search for:

will automatically return all three records:

When to use Expand explicitly

When a field has not been configured as inherited a value in the parent record does not display in the child record BUT it is still possible to search for that value and return the parent record AND any of its children where the search field is empty.

And this is where we use the Expand operator.

In the Moving image catalogue example above, the Running time field (runtime.minutes (r3)) has not been configured as inherited. The Manifestation record has a runtime of 97 minutes (runtime.minutes (r3) = 97) and its child, the Item record, has no value in the runtime.minutes (r3) field.

A search for:

will return only the Manifestation record.

A search using the Expand operator (here we see the search statement in an Advanced search):

will return both records.

The short answer is the Expand operator: when performing a Standard or an Advanced search, the Expand operator makes it possible to search for records using one or more values inherited from a parent record (whether a field has been configured as inherited or not).

To demonstrate, the following hierarchy comprises 19 records:

Only the parent record has values specified for Date (early) and Date (late):

This search will return only one record:

If we use the Expand operator however (here we select the Expand checkbox in a Standard search), all 19 records will be returned:

This is a Boolean AND search and records are returned if:

• Reference Code begins with AC

  And

• Date (early) is 1901

By selecting the Expand option the search is expanded to child records and empty fields are assumed to have inherited the value from a parent record.

If a field in a child record is not empty, the Expand option will respect this. For example, we add a different Date (early) to one of the records in the hierarchy:

Now when we repeat our expanded search, only 13 records are returned as 6 records in the hierarchy no longer match the search criteria.

When a checkbox is not selected (it does not contain a tick), it is obviously empty and its value is NULL. When a checkbox is ticked, the value it holds is an x.

Here we see the Publish on web (publish_on_web (wp)) field on the Management details panel:

To search for records in which the Publish on web checkbox is unticked, we could perform the following Advanced search:

publish_on_web = ""

To return records where the Publish on web checkbox is ticked, we could perform either of the following searches:

publish_on_web = x

-OR-

publish_on_web = *

(return all records in which the Publish on web field contains a value).

Tip: Knowing that the value in a ticked checkbox is an x is important when performing a search and replace on a checkbox or when adding a tick to a checkbox when importing data.